package controle.professor;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.TimeZone;
import java.text.SimpleDateFormat;

import javax.swing.JOptionPane;

import controle.conexao;

import modelagem.Professor;
import modelagem.Sexo;

public class professorDAO {

	public long inserir(Professor professor) {
		long id = -1;
		
		DateFormat format = new SimpleDateFormat("dd/MM/yyyy", Locale.US);
		format.setTimeZone(TimeZone.getTimeZone("Etc/UTC"));

		java.util.Date date = null;
		try {
			date = format.parse(professor.getData_nasc());
		} catch (ParseException e1) {
			e1.printStackTrace();
		}

		java.sql.Date sqlDate = new java.sql.Date(date.getTime());
		
				
		String sql = "insert into cont_academico."
				+ "professor(nome, telefone, data_nasc," 
				+"sexo, rg, cpf, email, endereco, observacoes)" 
				+ "values(?,?,?,?,?,?,?,?,?)";
		
		Connection connection = conexao.getInstace().getConnection();
		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement(sql,
					PreparedStatement.RETURN_GENERATED_KEYS);
			statement.setString(1, professor.getNome());
			statement.setString(2, professor.getTelefone());
			statement.setDate(3, sqlDate);
			statement.setString(4, professor.getS().getSexo());
			statement.setString(5, professor.getRg());
			statement.setString(6, professor.getCpf());
			statement.setString(7, professor.getEmail());
			statement.setString(8, professor.getEndereco());
			statement.setString(9, professor.getObservacao());
			statement.executeUpdate();
			ResultSet set = statement.getGeneratedKeys();
			while (set.next()) {
				id = set.getLong("id");
			}
			JOptionPane.showMessageDialog(null, "Salvo com sucesso!");
		} catch (SQLException e) {
			e.printStackTrace();
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
		return id;
	}
	
	
	
	public List<Professor> buscar(String nome){
		String sql;
		Professor professor;
		List<Professor> lista = new ArrayList<Professor>();
		
		sql = "select * from cont_academico."
				+ "professor where nome like ? order by nome;";
		
		Connection connection = conexao.getInstace().getConnection();
		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement(sql);
			statement.setString(1, "%"+nome+"%");
			
			statement.execute();
			ResultSet set = statement.getResultSet();
			
			while (set.next()) {
				professor = new Professor();
				Sexo s;
			    SimpleDateFormat formatador = new SimpleDateFormat("dd/MM/yyyy");  
			    String dataFormatada = formatador.format(set.getDate("data_nasc"));  
				if(set.getString("sexo").equals("M"))
					s = Sexo.M;
				else
					s = Sexo.F;
				
				professor.setId(set.getInt("id"));
				professor.setNome(set.getString("nome"));
				professor.setCpf(set.getString("cpf"));
				professor.setRg(set.getString("rg"));
				professor.setS(s);
				professor.setEmail(set.getString("email"));
				professor.setTelefone(set.getString("telefone"));
				professor.setEndereco(set.getString("endereco"));
				professor.setData_nasc(dataFormatada);
				professor.setObservacao(set.getString("observacoes"));
				lista.add(professor);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
		return lista;
	}
	
	
	
	public void atualizar(Professor professor){
		DateFormat format = new SimpleDateFormat("dd/MM/yyyy", Locale.US);
		format.setTimeZone(TimeZone.getTimeZone("Etc/UTC"));

		java.util.Date date = null;
		try {
			date = format.parse(professor.getData_nasc());
		} catch (ParseException e1) {
			e1.printStackTrace();
		}

		java.sql.Date sqlDate = new java.sql.Date(date.getTime());
		
				
		String sql = "update cont_academico."
				+ "professor set (nome, telefone, data_nasc," 
				+"sexo, rg, cpf, email, endereco, observacoes)" 
				+ " = (?,?,?,?,?,?,?,?,?) where id = ?";
		
		Connection connection = conexao.getInstace().getConnection();
		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement(sql);
			statement.setString(1, professor.getNome());
			statement.setString(2, professor.getTelefone());
			statement.setDate(3, sqlDate);
			statement.setString(4, professor.getS().getSexo());
			statement.setString(5, professor.getRg());
			statement.setString(6, professor.getCpf());
			statement.setString(7, professor.getEmail());
			statement.setString(8, professor.getEndereco());
			statement.setString(9, professor.getObservacao());
			statement.setInt(10, professor.getId());
			statement.executeUpdate();
			
			JOptionPane.showMessageDialog(null, "Atualizado com sucesso!");
		} catch (SQLException e) {
			e.printStackTrace();
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
	}



	public Professor buscarPorid(int id_prof) {
		String sql;
		Professor professor = null;
		
		sql = "select * from cont_academico."
				+ "professor where id = ? order by nome;";
		
		Connection connection = conexao.getInstace().getConnection();
		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id_prof);
			
			statement.execute();
			ResultSet set = statement.getResultSet();
			
			while (set.next()) {
				professor = new Professor();
				Sexo s;
			    SimpleDateFormat formatador = new SimpleDateFormat("dd/MM/yyyy");  
			    String dataFormatada = formatador.format(set.getDate("data_nasc"));  
				if(set.getString("sexo").equals("M"))
					s = Sexo.M;
				else
					s = Sexo.F;
				
				professor.setId(set.getInt("id"));
				professor.setNome(set.getString("nome"));
				professor.setCpf(set.getString("cpf"));
				professor.setRg(set.getString("rg"));
				professor.setS(s);
				professor.setEmail(set.getString("email"));
				professor.setTelefone(set.getString("telefone"));
				professor.setEndereco(set.getString("endereco"));
				professor.setData_nasc(dataFormatada);
				professor.setObservacao(set.getString("observacoes"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
		return professor;
	}

}
